//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Shp.Runtime.Contract;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Globalization;
using System.Data.Common;
using System.Data;
using LitwareHR.Recruiting.Contract;
using System.Runtime.Remoting.Contexts;

namespace LitwareHR.Recruiting.DataModel.Sql
{
    public class RecruitingDataModel
    {
        public StringPair[][] GetRecruitingWorkflowListByApplicant(Guid tenantId, Upn upn)
        {
            List<StringPair[]> recruitingWorkflows = new List<StringPair[]>();

            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TrackingStore);
            string sql = string.Format(CultureInfo.CurrentCulture, Constants.DB.SqlForSelectRecruitingWorkflowIdsByApplicant, tenantId, upn.ToString());
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                DataSet ds = db.ExecuteDataSet(command);
                long eventId = 0;
                Guid workflowInstanceId = Guid.Empty;
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    eventId = (long)row["EventId"];
                    workflowInstanceId = (Guid)row["WorkflowInstanceId"];

                    string rpSql = string.Format(CultureInfo.CurrentCulture, Constants.DB.SqlForSelectRecruitingWorkflowListByWorkflowIdAndEventId, workflowInstanceId, eventId);
                    using (DbCommand commandWorkflow = db.GetSqlStringCommand(rpSql))
                    {
                        DataSet workflowDataSet = db.ExecuteDataSet(commandWorkflow);
                        string applicantUsername = "";
                        string waitingFor = "";
                        string nextStep = "";
                        foreach (DataRow workflowDataRow in workflowDataSet.Tables[0].Rows)
                        {
                            switch (workflowDataRow["FieldName"].ToString())
                            {
                                case "ApplicantUsername":
                                    applicantUsername = (string)workflowDataRow["Data_Str"].ToString();
                                    break;
                                case "WaitingFor":
                                    waitingFor = (string)workflowDataRow["Data_Str"];
                                    break;
                                case "NextStep":
                                    nextStep = (string)workflowDataRow["Data_Str"];
                                    break;
                            }
                        }
                        List<StringPair> submission = new List<StringPair>();
                        submission.Add(new StringPair("WorkflowId", workflowInstanceId.ToString()));
                        submission.Add(new StringPair("Applicant", applicantUsername));
                        submission.Add(new StringPair("Waiting For", waitingFor));
                        submission.Add(new StringPair("NextStep", nextStep));
                        recruitingWorkflows.Add(submission.ToArray());
                    }
                }
            }

            return recruitingWorkflows.ToArray();
        }

        public StringPair[][] GetRecruitingWorkflowList(Guid tenantId)
        {
            List<StringPair[]> recruitingWorkflows = new List<StringPair[]>();

            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TrackingStore);
            string sql = string.Format(CultureInfo.CurrentCulture, Constants.DB.SqlForSelectRecruitingWorkflowIds, tenantId);
            using (DbCommand command = db.GetSqlStringCommand(sql))
            {
                DataSet ds = db.ExecuteDataSet(command);
                long eventId = 0;
                Guid workflowInstanceId = Guid.Empty;
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    eventId = (long)row["EventId"];
                    workflowInstanceId = (Guid)row["WorkflowInstanceId"];

                    string rpSql = string.Format(CultureInfo.CurrentCulture, Constants.DB.SqlForSelectRecruitingWorkflowListByWorkflowIdAndEventId, workflowInstanceId, eventId);
                    using (DbCommand commandWorkflow = db.GetSqlStringCommand(rpSql))
                    {
                        DataSet workflowDataSet = db.ExecuteDataSet(commandWorkflow);
                        string applicantUsername = string.Empty;
                        string waitingFor = string.Empty;
                        string nextStep = string.Empty;
                        string positionId = string.Empty;
                        string resumeId = string.Empty;
                        foreach (DataRow workflowDataRow in workflowDataSet.Tables[0].Rows)
                        {
                            string fieldName = workflowDataRow["FieldName"].ToString();
                            switch (fieldName)
                            {
                                case "ApplicantUsername":
                                    applicantUsername = workflowDataRow["Data_Str"].ToString();
                                    break;
                                case "WaitingFor":
                                    waitingFor = workflowDataRow["Data_Str"].ToString();
                                    break;
                                case "NextStep":
                                    nextStep = workflowDataRow["Data_Str"].ToString();
                                    break;
                                case "PositionId":
                                    positionId = workflowDataRow["Data_Str"].ToString();
                                    break;
                                case "ResumeId":
                                    resumeId = workflowDataRow["Data_Str"].ToString();
                                    break;
                            }
                        }
                        List<StringPair> submission = new List<StringPair>();
                        submission.Add(new StringPair("WorkflowId", workflowInstanceId.ToString()));
                        submission.Add(new StringPair("Applicant", applicantUsername));
                        submission.Add(new StringPair("Waiting For", waitingFor));
                        submission.Add(new StringPair("NextStep", nextStep));
                        submission.Add(new StringPair("PositionId", positionId.ToString()));
                        submission.Add(new StringPair("ResumeId", resumeId.ToString()));
                        recruitingWorkflows.Add(submission.ToArray());
                    }
                }
            }
            return recruitingWorkflows.ToArray();
        }

        public Resume GetResume(Guid resumeId)
        {         
            
            Resume resume = new Resume(); ;

            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);
            DbCommand command = db.GetStoredProcCommand(Constants.DB.SqlGetResume);
            db.AddInParameter(command, "@resumeId", DbType.Guid);
            db.SetParameterValue(command, "@resumeId", resumeId);
            IDataReader reader = db.ExecuteReader(command);

            using (reader as IDisposable)
            {
                while (reader.Read())
                {
                    resume.Id = reader.GetGuid(0);
                    resume.PositionId = reader.GetGuid(1);
                    resume.Upn = new Upn(reader.GetString(2));
                    if (!reader.IsDBNull(3))
                        resume.Objectives = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        resume.Interests = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                    {
                        resume.IsVideoAvailable = reader.GetBoolean(5);
                    }
                    if (!reader.IsDBNull(6))
                    {
                        resume.IsVideoUploaded = reader.GetBoolean(6);
                    }
                }
                reader.Close();
            }

            return resume;
        }

        public void SubmitResume(Resume resume)
        {         
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);
            db.ExecuteNonQuery(Constants.DB.SqlSubmitResume, resume.Id, resume.PositionId, resume.Upn.ToString(), resume.Objectives, resume.Interests, resume.IsVideoAvailable, resume.IsVideoUploaded);
            
        }

        public void AddVideoResume(Resume resume)
        {            
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);           
            db.ExecuteNonQuery(Constants.DB.SqlAddVideoResume, resume.Id, true);                                    
        }

        public void SetErrorOnVideoResume(Resume resume)
        {           
            Database db = DatabaseFactory.CreateDatabase(Constants.DB.TenantDataStore);
            db.ExecuteNonQuery(Constants.DB.SqlSetErrorVideoUpload, resume.Id);
        }

        public SilverlightAccount GetSilverlightAccount(Guid tenantId)
        {
            Database metaDb = DatabaseFactory.CreateDatabase(Constants.DB.TenantMetadataStore);
            DbCommand command = metaDb.GetStoredProcCommand(Constants.DB.SqlGetSilverlightAccount);
            metaDb.AddInParameter(command, "@tenantId", DbType.Guid);
            metaDb.SetParameterValue(command, "@tenantId", tenantId);
            IDataReader reader = metaDb.ExecuteReader(command);

            using (reader as IDisposable)
            {
                if (reader.Read())
                {
                    SilverlightAccount slAccount = new SilverlightAccount();

                    slAccount.AccountId = reader.GetString(0);
                    slAccount.AccountKey = reader.GetString(1);
                    slAccount.TenantAlias = reader.GetString(2);

                    reader.Close();
                    return slAccount;
                }
            }

            return null;
        }

    }
}
